package uniLearn.control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import uniLearn.data.Units;
import uniLearn.data.Users;
import uniLearn.data.UsersType;

public class UnitsControl extends DataControl {
	//define
	//SQL command
	private static String SQL_GET_UNIT = "SELECT `name` FROM `unit` WHERE `id` = ?;";
	private static String SQL_GET_ALL_UNIT = "SELECT `id`, `name` FROM `unit` ORDER BY `unit`.`name` ASC";
	private static String SQL_GET_USER_FROM_UNIT = "SELECT `id`, `name`, `userType` FROM `users`, `users_unit` WHERE `user_id` = `id` AND `unit_id` = ?";
	private static String SQL_CHECK_ID = "SELECT COUNT(*) FROM `unit` WHERE `id` = ?;";
	
	private static String SQL_CREATE_UNIT = "INSERT INTO `unit` (`id`, `name`) VALUES (?, ?);";
	
	private static String SQL_INSERT_USER_UNIT = "INSERT INTO `users_unit` (`user_id`, `unit_id`) VALUES (?, ?);";
	private static String SQL_CLEAR_USER_UNIT = "DELETE FROM `users_unit` WHERE `unit_id` = ?;";
	private static String SQL_CREATE_SUBMIT_ASSIGNMENT = "INSERT IGNORE INTO `submit_assignment` (`ac_id`, `submitBy`, `submitTime`, `fileName`) "+
			"SELECT `a`.`id` AS `ac_id`, `u`.`id` AS `submitBy`, NOW() AS `submitTime`, '' AS `fileName` "+
			"FROM `users` AS `u`, `users_unit`, `assignment_collector` AS `a` "+
			"WHERE `users_unit`.`unit_id` = `a`.`unit_id` AND `user_id` = `u`.`id` "+  
			"AND `userType` = 'student' AND `a`.`unit_id` = ? ;"; //1 = unit_id
	
	
	public ArrayList<Units> getAllUnit() throws Exception {
		ArrayList<Units> listU = new ArrayList<Units>();
		
		//Start Connection
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_ALL_UNIT);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		while(rs.next()) {
			String id = rs.getString("id");
			String name = rs.getString("name");
			listU.add(new Units(id, name));
		}
		
		return listU;
	}
	
	public Units getUnit(String uID) throws Exception {
		if(checkLength(uID, Units.ID_MAX_LENGTH))
			throw new Exception("Incorrect Assignment ID");
		
		Units unit = null;
		//Start Connection
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_UNIT);
		// set parameter
		preStmt.setString(1, uID);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		if(rs.next()) {
			unit = new Units(uID, rs.getString("name"));
		}
		else {
			rs.close();
			preStmt.close();
			throw new Exception("Can't find Unit");
		}
		
		rs.close();
		preStmt.close();
		return unit;
	}
	
	public ArrayList<Users> getUsersFromUnit(String unitId) throws Exception {
		if(checkLength(unitId, Units.ID_MAX_LENGTH))
			throw new Exception("Incorrect unit format");
		
		ArrayList<Users> listUsers = new ArrayList<Users>();
		//Start Connection
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_GET_USER_FROM_UNIT);
		// set parameter
		preStmt.setString(1, unitId);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		while(rs.next()) {
			String userId = rs.getString("id");
			String userName = rs.getString("name");
			UsersType type = UsersType.fromString(rs.getString("userType"));
			Users aUser = new Users(userId, userName, type);
			
			listUsers.add(aUser);
		}
		
		preStmt.close();
		rs.close();
		
		return listUsers;
	}
	
	public void createUnit(Units newUnit) throws Exception {
		if(newUnit == null || checkLength(newUnit.getId(), Units.ID_MAX_LENGTH) || checkLength(newUnit.getName(), Units.NAME_MAX_LENGTH))
			throw new Exception("Incorrect unit format");
		
		//Start Connection
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_CHECK_ID);
		//set parameter
		preStmt.setString(1, newUnit.getId());
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		int count = -1;
		if(rs.next())
			count = rs.getInt(1);
		
		rs.close();
		preStmt.close();
		
		if(count < 0)
			throw new Exception("Can't check the unit ID");
		if(count > 0)
			throw new Exception("Duplicate ID found");
		
		preStmt = this.dbCon.preStmt(SQL_CREATE_UNIT);
		preStmt.setString(1, newUnit.getId());
		preStmt.setString(2, newUnit.getName());
		
		count = preStmt.executeUpdate();
		
		preStmt.close();
		
		if(count < 1)
			throw new Exception("Can't Create an new unit");
	}
	
	public void updateUserInUnit(String unitId, String[] userIDs) throws Exception {
		if(checkLength(unitId, Units.ID_MAX_LENGTH))
			throw new Exception("Incorrect unit input");
		
		int length = 0;
		if(userIDs != null)
			length = userIDs.length;
		
		for(int i = 0; i < length; i++)
			if(checkLength(userIDs[i], Users.ID_MAX_LENGTH))
				throw new Exception("Incorrect users input");
		
		this.startConnection();
		//Start Connection
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_CHECK_ID);
		//set parameter
		preStmt.setString(1, unitId);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		int count = -1;
		if(rs.next())
			count = rs.getInt(1);
		
		rs.close();
		preStmt.close();
		
		if(count < 0)
			throw new Exception("Can't check the unit ID");
		if(count == 0)
			throw new Exception("Unit ID: "+unitId + " not found");
		
		//Remove all user from users_unit
		preStmt = this.dbCon.preStmt(SQL_CLEAR_USER_UNIT);
		//set parameter
		preStmt.setString(1, unitId);
		//Run SQL
		preStmt.executeUpdate();
		preStmt.close();
		
		//If no user id input, remove all user only
		if(length == 0)
			return;
		
		//Insert user into users_unit
		count = 0;
		preStmt = this.dbCon.preStmt(SQL_INSERT_USER_UNIT);
		//set parameter
		preStmt.setString(2, unitId);
		
		for(int i = 0; i < length; i++) {
			preStmt.setString(1, userIDs[i]);
			//Run SQL
			count += preStmt.executeUpdate();
		}
		preStmt.close();
		
		if(count != length)
			throw new Exception("Error in Insert user into unit");
		
		//Insert default submit assignment
		preStmt = this.dbCon.preStmt(SQL_CREATE_SUBMIT_ASSIGNMENT);
		//set parameter
		preStmt.setString(1, unitId);
		//Run SQL
		preStmt.executeUpdate();
		preStmt.close();
	}
}
